CD BIT 75/CD BIT 75.iso/Software/SinfoSeguros/MSDE/MSM/REPL.MSM/MergeModule.CABinet/sp3ServUni.sql.185C1D8F_1545_4277_BB64_857D2622DB57

Text (UTF-16) | 2002-12-11 | 102.5 KB | 1,796 lines

/*------------------------------------------------------------------------------

SP3_SERV.SQL

THIS SCRIPT TAKES THE SERVER-SIDE SYSTEM-PROCS FROM 8.0SP2 to SP3.

Changes in this file are organized as follows (please maintain):
    System Tables (UPGRADE.SQL)
    System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL)
    Engine System Procs (U_TABLES.SQL / PROCSYST.SQL / ODSOLE.SQL)
    Schema Procs (OLEDBSCH.SQL / ANSIVIEW.SQL)
    ODBC/OLEDB Catalog Procs (INSTCAT.SQL)
    SEM SQLDMO System Procs (SQLDMO.SQL)

Changes to these scripts should NOT be placed in this file:
    Starfighter Procs (XPSTAR.SQL / INSTMSDB.SQL / SQLTRACE.SQL / WEB.SQL)
    Doc's Samples (INSTPUBS.SQL / INSTNWND.SQL)
    Replication Procs (REPLSYS.SQL / REPLCOM.SQL / REPLTRAN.SQL / REPLMERG.SQL)
These components will maintain separate upgrade scripts.

Notes:
+ Catalog-updates and sp_MS_upd_sysobj_category are enabled for the entire
    file.  Do not disable or re-enable them.  Please do not change set options.

------------------------------------------------------------------------------*/


--------------------------------------------------------------------------------
-- VERIFY Server is started in single-user-mode (catalog-updates enables), and
--    start marking of system-objects.
--------------------------------------------------------------------------------
execute sp_configure 'allow updates',1
go

reconfigure with override
go

exec sp_MS_upd_sysobj_category 1
go

--------------------------------------------------------------------------------
--    System Tables (UPGRADE.SQL)
--------------------------------------------------------------------------------

IF OBJECT_ID('sysprocesses') IS NOT NULL
    DROP TABLE sysprocesses
go
CREATE TABLE sysprocesses (     -- see ntdbms\storeng\dfs\startup\dataserv.c
    spid            smallint NOT NULL,
    kpid            smallint NOT NULL,
    blocked         smallint NOT NULL,
    waittype        binary(2) NOT NULL,
    waittime        int NOT NULL,
    lastwaittype    nchar(32) NOT NULL,
    waitresource    nchar(256) NOT NULL,
    dbid            smallint NOT NULL,
    uid             smallint NOT NULL,
    cpu             int NOT NULL,
    physical_io     bigint NOT NULL,
    memusage        int NOT NULL,
    login_time      datetime NOT NULL,
    last_batch      datetime NOT NULL,
    ecid            smallint NOT NULL,
    open_tran        smallint NOT NULL,
    status          nchar(30) NOT NULL,
    sid             binary(86) NOT NULL,
    hostname        nchar(128) NOT NULL,
    program_name    nchar(128) NOT NULL,
    hostprocess     nchar(8) NOT NULL,
    cmd             nchar(16) NOT NULL,
    nt_domain       nchar(128) NOT NULL,
    nt_username     nchar(128) NOT NULL,
    net_address     nchar(12) NOT NULL,
    net_library     nchar(12) NOT NULL,
    loginame        nchar(128) NOT NULL,
    context_info    binary(128) NOT NULL,
    sql_handle        binary(20) NOT NULL,
    stmt_start        int NOT NULL,
    stmt_end        int NOT NULL
   )
go
GRANT SELECT ON sysprocesses TO PUBLIC
go
delete from sysconfigures where config = 400 and comment = N'Cross DB Ownership Chaining'
go
INSERT sysconfigures (config, status, value, comment) VALUES ( 400,  1 ,     0, N'Cross DB Ownership Chaining' )
go

--------------------------------------------------------------------------------
--    System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL) English
--------------------------------------------------------------------------------
DELETE sysmessages
 WHERE error in (
     211
    ,569
    ,570
    ,1229
    ,1620
    ,1621
    ,1836
    ,3124
    ,4454
    ,4455
    ,5600
    ,5601
    ,14360
    ,14361
    ,14362
    ,14363
    ,14364
    ,17176
    ,17177
    ,17883
    ,17884
    ,18779
    ,19030
    ,19031
    ,19032
    ,19033
    ,19034
    ,20624
    ,20625
    ,21695
    )
go

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (211, 23, 1, N'Possible schema corruption. Run DBCC CHECKCATALOG.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (569, 16, 0, N'The handle passed to fn_get_sql was invalid.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (570, 16, 0, N'INSTEAD OF triggers do not support direct recursion. Trigger execution failed.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (1229, 10, 0, N'Process ID %d:%d owns resources that are blocking processes on Scheduler %d.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (1836, 10, 0, N'Could not create default data file because the name was too long.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (3124, 14, 0, N'Must be a System Administrator to perform LOAD with CONVERT65.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (4454, 16, 0, N'View ''%.*ls'' is not updatable because the %s statement contains a GROUP BY ALL clause.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (4455, 16, 0, N'Derived table ''%.*ls'' is not updatable because the %s statement contains a GROUP BY ALL clause.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (5600,16,0,'The Cross Database Chaining option cannot be set to the specified value on the specified database.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (14360, 16, 0, N'%s is already configured as TSX machine', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (14361, 16, 0, N'MSX server does not support mixed security mode', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (14362, 16, 0, N'The MSX server must be running the Standard or Enterprise edition of SQL Server', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (14363, 16, 0, N'The MSX server is not prepared for enlistments [there must be an operator named ''MSXOperator'' defined at the MSX]', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (14364, 16, 0, N'The TSX server is not currently enlisted', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (17176, 10, 128, N'This instance of SQL Server last reported using a process id of %1 at %2 (local) %3 (UTC).', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (17177, 10, 128, N'This instance of SQL Server has been using a process id of %1 since %2 (local) %3 (UTC).', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (17883, 10, 128, N'The Scheduler %1!ld! appears to be hung. SPID %2!ld!, ECID %3!ld!, UMS Context 0x%4!p!', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (17884, 10, 128, N'Potential deadlocks exist on all the schedulers', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (18779, 16, 0, N'%s: Number of delete and insert log records mismatch in bounded update, insert:%d, delete:%d, beginLSN:(%ls), endLSN(%ls)', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (19030, 10, 128, N'SQL Trace started.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (19031, 10, 128, N'SQL Trace stopped.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (19032, 10, 128, N'SQL Trace stopped due to server shutdown.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (19033, 10, 128, N'Server started with ''-f''. Auditing will not be started.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (19034, 16, 128, N'Cannot start C2 audit trace. SQL Server is shutting down.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (20624, 16, 0, N'Server user ''%s'' is not a valid user in database ''%s''. Add the user account into the database first.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (20625, 16, 0, N'Could not create the merge replication PAL database role for publication ''%s''.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (21695, 10, 0, N'The job name ''%s'' was not generated for this replication agent, delete the job manually when it is no longer in use.', 1033)

--------------------------------------------------------------------------------
--    System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL) Localized
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--    Engine System Procs (U_TABLES.SQL / PROCSYST.SQL / ODSOLE.SQL)
--------------------------------------------------------------------------------

if exists (select * from sysobjects
       where xtype = N'TF' and uid = USER_ID('system_function_schema') and
        name = 'fn_get_sql')
    drop function system_function_schema.fn_get_sql
go

raiserror(15339,-1,-1,'system_function_schema.fn_get_sql')
go
create function system_function_schema.fn_get_sql
    (
    @handle binary(20)
    )
returns @tab table(dbid SmallInt,
    objectid Int,
    number SmallInt,
    encrypted Bit NOT NULL,
    text Text)
as
begin
    insert @tab
    select * from OpenRowset(FnGetSql, @handle)

    return
end -- fn_get_sql
go

if object_id('sp_fulltext_catalog','P') IS NOT NULL
    drop procedure sp_fulltext_catalog

---------------------------- sp_fulltext_catalog ------------------------------

raiserror(15339,-1,-1,'sp_fulltext_catalog')
go
create proc sp_fulltext_catalog
    @ftcat        sysname,        -- full-text catalog name
    @action     varchar(20),    -- create | drop | | rebuild | ...
    @path        nvarchar(101) = null    -- optional file path for create (max of 100 chars!!!)
as
    declare @objname sysname,
            @objid    int,
            @vc1    nvarchar(517),
            @tabname    nvarchar(517),
            @tabwarn int

    select @tabwarn = 0

    -- FULLTEXT MUST BE ACTIVE IN DATABASE --
    if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
    begin
        raiserror(15601,-1,-1)
        return 1
    end

    -- VALIDATE PARAMS --
    if @action is null
        OR @action not in ('create','drop','start_full','start_incremental','stop','rebuild')
        OR @ftcat is null OR len(@ftcat) = 0
        OR (@path is not null and @action <> 'create')
        OR (len(@path) > 100 )
    begin
        raiserror(15600,-1,-1,'sp_fulltext_catalog')
        return 1
    end

    -- DISALLOW USER TRANSACTION --
    set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sp_fulltext_catalog')
        return 1
    end

    -- CHECK PERMISSIONS (must be a dbowner) --
    if (is_member('db_owner') = 0)
    begin
        raiserror(15247,-1,-1)
        return 1
    end

    -- CHECK DATABASE MODE (must not be read-only) --
    if DATABASEPROPERTY(db_name(), 'IsReadOnly') = 1
    begin
        raiserror(15635, -1, -1, 'sp_fulltext_catalog')
        return 1
    end

    -- CATALOG MUST EXIST IF NOT CREATING --
    declare @ftcatid smallint
    select @ftcatid = ftcatid from sysfulltextcatalogs where name = @ftcat
    if @action not in ('create', 'drop') and @ftcatid is null
    begin
        raiserror(7641,-1,-1,@ftcat)
        return 1
    end

    if @action = 'create'
    begin
        DBCC CALLFULLTEXT ( 1, @ftcat, @path )    -- FTCreateCatalog( @ftcat, @path )
        if @@error <> 0
            return 1
    end

    if @action = 'drop'
    begin
        -- CANNOT DROP CATALOG IF USED --
        if exists (select * from sysobjects where ftcatid = @ftcatid)
        begin
            raiserror(15604,-1,-1, @ftcat)
            return 1
        end

        DBCC CALLFULLTEXT ( 2, @ftcat )    -- FTDropCatalog( @ftcat )
        if @@error <> 0
            return 1
    end

    if @action = 'start_full'
    begin
        -- ERROR IF DATABASE IS IN SINGLE USER MODE --
        if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
        begin
            raiserror(15636, -1, -1, @ftcat)
            return 1
        end

        begin tran
        -- MARK TABLES/URLs AS --
        declare ms_crs_ftind cursor static local for select id, name from sysobjects
                    where ftcatid = @ftcatid
        open ms_crs_ftind
        fetch ms_crs_ftind into @objid, @tabname
        while @@fetch_status >= 0
        begin

            -- ERROR ON TABLE IF TABLE IS NOT ACTIVATED --
            if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
            begin
                raiserror(15630, -1, -1, @tabname)
                goto error_exit
            end

            -- SKIP TABLE IF CRAWL ALREADY IN PROGRESS --
            if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
            begin
                select @tabwarn = 1
                fetch ms_crs_ftind into @objid, @tabname
                continue
            end


            -- START FULL CRAWL
            DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
            if @@error <> 0    -- server raised an error
            begin
                -- server did an ex_raise - this is unreachable code
                goto error_exit
            end

            -- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
            delete sysfulltextnotify where tableid = @objid

            if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
            and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
            begin

                select @vc1 = quotename(user_name(OBJECTPROPERTY(@objid,'OwnerId'))) + '.'
                            + quotename(object_name(@objid))

                dbcc lockobjectschema(@vc1)

                update sysobjects set status = status & ~128 where id = @objid

            end

            fetch ms_crs_ftind into @objid, @tabname
        end
        deallocate ms_crs_ftind

        commit tran

    end

    if @action = 'start_incremental'
    begin
        -- ERROR IF DATABASE IS IN SINGLE USER MODE --
        if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
        begin
            raiserror(15636, -1, -1, @ftcat)
            return 1
        end
        begin tran

        -- MARK TABLES/URLs AS --
        declare ms_crs_ftind cursor static local for select id, name from sysobjects
                    where ftcatid = @ftcatid
        open ms_crs_ftind
        fetch ms_crs_ftind into @objid, @tabname
        while @@fetch_status >= 0
        begin
            -- ERROR ON TABLE IF TABLE IS NOT ACTIVATED --
            if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
            begin
                raiserror(15630, -1, -1, @tabname)
                goto error_exit
            end

            -- SKIP TABLE IF CRAWL ALREADY IN PROGRESS --
            if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
            begin
                select @tabwarn = 1
                fetch ms_crs_ftind into @objid, @tabname
                continue
            end

            if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
            and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
            begin

                -- START A FULL POPULATION FOR THIS TABLE --
                DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
                if @@error <> 0
                begin
                    -- server did an ex_raise - this is unreachable code --
                    goto error_exit
                end

                select @vc1 = quotename(user_name(OBJECTPROPERTY(@objid,'OwnerId'))) + '.'
                        + quotename(object_name(@objid))

                dbcc lockobjectschema(@vc1)

                update sysobjects set status = status & ~128 where id = @objid

            end
            else
            begin
                -- START AN INCREMENTAL POPULATION FOR THIS TABLE --
                DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 1 )
                if @@error <> 0
                begin
                    -- server did an ex_raise - this is unreachable code --
                    goto error_exit
                end

            end

            -- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
            delete sysfulltextnotify where tableid = @objid

            fetch ms_crs_ftind into @objid, @tabname
        end
        deallocate ms_crs_ftind
        commit tran


    end

    if @action = 'stop'
    begin
        declare ms_crs_ftind cursor static local for select id, name from sysobjects
                    where ftcatid = @ftcatid
        open ms_crs_ftind
        fetch ms_crs_ftind into @objid, @tabname
        while @@fetch_status >= 0
        begin

            -- ERROR ON TABLE IF TABLE IS NOT ACTIVATED --
            if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
            begin
                raiserror(15630, -1, -1, @tabname)
                return 1
            end

            -- SKIP TABLE IF CRAWL ALREADY STOPPED - NO WARNING --
            if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 0)
            begin
                fetch ms_crs_ftind into @objid, @tabname
                continue
            end

            -- ERROR IF POPULATE STATUS OF THE TABLE IS CRAWLING AND CT ON
            if (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1)
            and ((ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 1)
            or (ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 2))
            begin
                raiserror(15642,-1,-1, @tabname)
                return 1
            end

            -- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
            DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
            if @@error <> 0
            begin
                -- server did an ex_raise - this is unreachable code --
                return 1
            end

            fetch ms_crs_ftind into @objid, @tabname
        end
        deallocate ms_crs_ftind

    end

    if @action = 'rebuild'
    begin

        -- RE-CREATE CATALOG (Will first drop)
        select @path = path from sysfulltextcatalogs where ftcatid = @ftcatid
        DBCC CALLFULLTEXT ( 16, @ftcat, @path )    -- FTCreateCatalog( @ftcat, @path )
        if @@error <> 0
        begin
            -- server did an ex_raise - this is unreachable code --
            return 1
        end

        begin tran

        -- RE-ACTIVATE TABLES/URLs --
        declare ms_crs_ftind cursor static local for select id from sysobjects
                    where ftcatid = @ftcatid
        open ms_crs_ftind
        fetch ms_crs_ftind into @objid
        while @@fetch_status >= 0
        begin
            DBCC CALLFULLTEXT ( 5, @ftcatid, @objid )    -- FTAddURL( @ftcat, db_id(), @objid )
            if @@error <> 0
            begin
                -- server did an ex_raise - this is unreachable code --
                goto error_exit
            end

            -- CHECK TABLE FOR NOTIFICATIONS --
            if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1
                and ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
            begin
                -- ERROR IF DATABASE IS IN SINGLE USER MODE --
                if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
                begin
                    select @objname = object_name(@objid)
                    raiserror(15638, -1, -1, @objname)

                    select @vc1 = quotename(user_name(OBJECTPROPERTY(@objid,'OwnerId'))) + '.'
                        + quotename(@objname)


                    dbcc lockobjectschema(@vc1)

                    -- DISABLE FULLTEXT AUTO PROPAGATION (NO ERROR IF ALREADY DISABLED AND --
                    -- IGNORE ANY OTHER ERRORS) --
                    DBCC CALLFULLTEXT ( 9, @objid )    -- FTDisableNotify( db_id(), @objid )
                    if @@error <> 0
                    begin
                        -- server did an ex_raise - this is unreachable code --
                        goto error_exit
                    end

                    -- TURN OFF CHANGE TRACKING ACTIVE BITS IN SYSOBJECTS --
                    update sysobjects set status = status & ~192 where id = @objid

                    fetch ms_crs_ftind into @objid
                    continue
                end

                -- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
                DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
                if @@error <> 0
                begin
                    -- server did an ex_raise - this is unreachable code --
                    goto error_exit
                end

                -- START A FULL CRAWL FOR THIS TABLE --
                DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
                if @@error <> 0
                begin
                    -- server did an ex_raise - this is unreachable code --
                    goto error_exit
                end

                -- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
                delete sysfulltextnotify where tableid = @objid

            end

            -- CHECK TABLE FOR AUTOPROPAGATION  -
            if ObjectProperty(@objid, 'TableFulltextAutoPropagationOn') = 1
                and ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
            begin
                DBCC CALLFULLTEXT ( 10, @ftcatid, @objid )    -- FTEnableAutoProp( @ftcatid, db_id(), @objid )
                if @@error <> 0
                begin
                    -- server did an ex_raise - this is unreachable code --
                    goto error_exit
                end
            end

            fetch ms_crs_ftind into @objid
        end
        deallocate ms_crs_ftind

        commit tran

    end
    if(@tabwarn <> 0)
    begin
        raiserror(15643, -1, -1)
        return 0
    end
    return 0    -- sp_fulltext_catalog

error_exit:
    -- 'stop', 'rebuild' never get here, this is only for 'start_full', 'start_incr'
    -- here we commit the changes for all tables on which the operation succeeded.
    -- Before 'goto error_exit' is called, schema changes made to table currently
    -- under cursor must be undone (so far, no schema changes)
    commit tran
    return 1    -- sp_fulltext_catalog

go

grant execute on sp_fulltext_catalog to public
go


--------------------------------------------------------------------------------
-- sp_change_users_login
--------------------------------------------------------------------------------
if object_id('sp_change_users_login','P') IS NOT NULL
    drop procedure sp_change_users_login
go

raiserror(15339,-1,-1,'sp_change_users_login')
go
CREATE PROCEDURE sp_change_users_login
    @Action               varchar(10)       -- REPORT / UPDATE_ONE / AUTO_FIX
   ,@UserNamePattern      sysname  = Null
   ,@LoginName            sysname  = Null
   ,@Password              sysname  = Null
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @exec_stmt nvarchar(430)

    declare @ret            int,
            @FixMode        char(5),
            @cfixesupdate   int,        -- count of fixes by update
            @cfixesaddlogin int,        -- count of fixes by sp_addlogin
            @dbname         sysname,
            @loginsid       varbinary(85),
            @110name        sysname

    -- SET INITIAL VALUES --
    select  @dbname         = db_name(),
            @cfixesupdate   = 0,
            @cfixesaddlogin = 0

    -- ERROR IF IN USER TRANSACTION --
    if @@trancount > 0
    begin
        raiserror(15289,-1,-1)
        return (1)
    end

    -- INVALIDATE USE OF SPECIAL LOGIN/USER NAMES --
    if suser_sid(@LoginName) = 0x1    -- 'sa'
    begin
        raiserror(15287,-1,-1,@LoginName)
        return (1)
    end
    if user_id(@UserNamePattern) in (1,0,3,4) --dbo, public, INFORMATION_SCHEMA, system_function_schema
    begin
        raiserror(15287,-1,-1,@UserNamePattern)
        return (1)
    end

    -- HANDLE REPORT --
    if upper(@Action) = 'REPORT'
    begin

        -- VALIDATE PARAMS --
        if @UserNamePattern IS NOT Null or @LoginName IS NOT Null
        begin
            raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
            return (1)
        end

        -- GENERATE REPORT --
        select UserName = name, UserSID = sid from sysusers
            where issqluser = 1 and (sid is not null and sid <> 0x0)
                    and suser_sname(sid) is null
            order by name
        return (0)
    end

    -- HANDLE UPDATE_ONE --
    if upper(@Action) = 'UPDATE_ONE'
    begin

        -- CHECK PERMISSIONS --
        if not is_member('db_owner') = 1
        begin
            raiserror(15247,-1,-1)
            return (1)
        end

        -- ERROR IF PARAMS NULL --
        if @UserNamePattern IS Null or @LoginName IS Null
        begin
            raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
            return (1)
        end

        -- VALIDATE PARAMS --
        -- Can ONLY remap SQL Users to SQL Logins!  Should be no need
        --  for re-mapping NT logins, and if you try, you'll mess up
        --  the user status bits!
        if not exists (select name from sysusers where
                name = @UserNamePattern             -- match user name
            and issqluser = 1)                      -- must be sql user
        begin
            raiserror(15291,-1,-1,'User',@UserNamePattern)
            return (1)
        end
        select @loginsid = sid from master.dbo.syslogins where
                loginname = @LoginName              -- match login name
            and isntname = 0                        -- cannot use nt logins
        if @loginsid is null
        begin
            raiserror(15291,-1,-1,'Login',@LoginName)
            return (1)
        end

        -- ERROR IF SID ALREADY IN USE IN DATABASE --
        if exists (select sid from sysusers where sid = @loginsid
                    and name <> @UserNamePattern)
        begin
            raiserror(15063,-1,-1)
            return (1)
        end

        -- CHANGE THE USERS LOGIN (SID) --
        update sysusers set sid = @loginsid, updatedate = getdate()
                where name = @UserNamePattern and issqluser = 1
                and sid <> @loginsid

        -- FINALIZATION: REPORT (ONLY IF NOT SUCCESSFUL) AND EXIT --
        if @@error <> 0 or @@rowcount <> 1
            raiserror(15295,-1,-1, 0)
        return (0)
    end

    -- ERROR IF NOT AUTO_FIX --
    if upper(@Action) <> 'AUTO_FIX'
    begin
        raiserror(15286,-1,-1,@Action)
        return (1)
    end

    -- HANDLE AUTO_FIX --
    -- CHECK PERMISSIONS --
    if not is_srvrolemember('sysadmin') = 1
    begin
        raiserror(15247,-1,-1)
        return (1)
    end

    -- VALIDATE PARAMS --
    if @UserNamePattern IS Null or @LoginName IS NOT Null
    begin
        raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
        return (1)
    end

    -- LOOP THRU ORPHANED USERS --
    select @exec_stmt = 'DECLARE ms_crs_110_Users cursor global for
            select name from sysusers
            where name = N' + quotename( @UserNamePattern , '''')+ '
                and issqluser = 1 and suser_sname(sid) is null'
    EXECUTE (@exec_stmt)
    OPEN ms_crs_110_Users

    WHILE (110=110)
    begin
        FETCH next from ms_crs_110_Users into @110name
        if (@@fetch_status <> 0)
        begin
            DEALLOCATE ms_crs_110_Users
            BREAK
        end

        -- IS NAME ALREADY IN USE? --
        -- if suser_sid(@110name) is null
        if not exists(select * from master.dbo.syslogins where loginname = @110name)
        begin

           -- VALIDATE PARAMS --
            if @Password IS Null
            begin
                raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
                return (1)
            end

            -- ADD LOGIN --
            execute @ret = sp_addlogin @110name, @Password, @dbname
            if @ret <> 0 or suser_sid(@110name) is null
            begin
                raiserror(15497,16,1,@110name)
                deallocate ms_crs_110_Users
                return (1)
            end
            select @FixMode = '1AddL'
            raiserror(15293,-1,-1,@110name)
        end
        ELSE
        begin
            Select @FixMode = '2UpdU'
            Raiserror(15292,-1,-1,@110name)
        end

        -- REPORT ERROR & CONTINUE IF DUPLICATE SID IN DB --
        select @loginsid = suser_sid(@110name)
        if user_sid(@loginsid) is not null
        begin
            raiserror(15331,-1,-1,@110name)
            CONTINUE
        end

        -- UPDATE SYSUSERS ROW --
        update sysusers set sid = @loginsid, updatedate = getdate(), status = (status & ~1) | 2 where name = @110name
        if @@error <> 0
        begin
            raiserror(15498,17,127)
            deallocate ms_crs_110_Users
            return (1)
        end


        if @FixMode = '1AddL'
            Select @cfixesaddlogin = @cfixesaddlogin + 1
        else
            Select @cfixesupdate = @cfixesupdate + 1
    end -- loop 110

    -- REPORT AND RETURN SUCCESS --
    raiserror(15295,-1,-1,@cfixesupdate)
    raiserror(15294,-1,-1,@cfixesaddlogin)
    return (0) -- sp_change_users_login
go


grant execute on sp_change_users_login to public
go
--------------------------------------------------------------------------------

------------------------------ sp_changedbowner -------------------------------
if object_id('sp_changedbowner','P') IS NOT NULL
    drop procedure sp_changedbowner
go

raiserror(15339,-1,-1,'sp_changedbowner')
go
create procedure sp_changedbowner
    @loginame       sysname,        -- login to become dbo
    @map            varchar(5) = NULL    -- True to map aliases, else drop
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @ret        int,
            @newsid     varbinary(85),
            @status     smallint

    -- CHECK PERMISSIONS (Note: All sysadmins are dbo) --
    -- See Bug Shiloh 362190 ---
    if not (is_srvrolemember('sysadmin') = 1)
    begin
        raiserror(15247,-1,-1)
        return(1)
    end

    -- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
    if db_name() in ('master', 'model', 'tempdb')
    begin
        raiserror(15109,-1,-1)
        return(1)
    end

    -- CHECK LOGIN NAME IS VALID (NT/SQL USER ONLY!) --
    select @newsid = sid, @status = 2 from master.dbo.syslogins
                    where loginname = @loginame and isntname = 0
    if @newsid is null
        select @status = 14, @newsid = get_sid('\U'+@loginame, NULL)
    if @newsid is null
    begin
        raiserror(15007,-1,-1,@loginame)
        return (1)
    end

    -- CHECK IF LOGIN ALREADY ALIASED IN DB --
    if exists (select sid from sysusers where isaliased = 1 and sid = @newsid)
    begin
        raiserror(15111,-1,-1)
        return (1)
    end

    -- CHECK IF LOGIN ALREADY KNOWN TO DATABASE --
    if exists (select sid from sysusers where sid = @newsid and uid <> 1)
    begin
        raiserror(15110,-1,-1)
        return (1)
    end

    -- MAKE THE FOLLOWING REMOVE/REMAP/DELETES ATOMIC --
    begin transaction

    -- REMAP DBO TO NEW SID --
    update sysusers set sid = @newsid, status = @status, updatedate = getdate()
            where name = 'dbo'

    -- REMOVE OTHER DBO-ALIASES IF REMAPPING NOT REQUESTED --
    if lower(@map) <> 'true'
    begin
        delete from sysusers where isaliased = 1 and altuid = user_id('dbo')
        raiserror(15500,-1,-1)
    end
    else
        raiserror(15499,-1,-1)     -- nothing to do to <remap>

    -- REFLECT NEW OWNER IN SYSDATABASES --
    update master.dbo.sysdatabases set sid = @newsid where dbid = db_id()
    commit transaction

    -- CHECKPOINT DATABASE TO FORCE CHANGES TO IN-MEMORY STRUCTURE --
    checkpoint
    raiserror(15501,-1,-1)
    grant all to null
    return (0) -- sp_changedbowner
go

grant execute on sp_changedbowner to public
go
--------------------------------------------------------------------------

delete from spt_values where name = 'CrossDbOwnChain' and number = 158 and type = 'T'
go
insert spt_values(name, number, type)
  values ('CrossDbOwnChain', 158, 'T')
go

delete from spt_values where name = 'Cross DB Ownership Chaining' and number = 400 and type = 'C'
go
insert spt_values(name, number, type, low, high,status)
    values ('Cross DB Ownership Chaining', 400, 'C', 0, 1, 0)
go

delete from spt_values where name = 'db chaining' and number = 0x400 and type = 'D2'
go
insert spt_values (name, number, type)
    values ('db chaining', 0x400, 'D2')
go

delete from spt_values where name = 'ALL SETTABLE OPTIONS' and type = 'D2'
go
insert spt_values (name, number, type)
    values ('ALL SETTABLE OPTIONS', 1469267968|0x800|0x1000|0x2000|0x400, 'D2')
go

delete from spt_values where name = 'ALL SETTABLE OPTIONS' and type = 'D'
go
insert spt_values (name, number, type)
    values ('ALL SETTABLE OPTIONS', 4202013, 'D')
go

------------------------------ sp_dboption -------------------------------
if object_id('sp_dboption','P') IS NOT NULL
    drop procedure sp_dboption
go

raiserror(15339,-1,-1,'sp_dboption')
go
/*ANSI_NULLS ON  for creation of sp_dboption*/
set ansi_nulls on
go
create procedure sp_dboption  -- 1999/08/09 18:25
@dbname sysname = NULL,     /* database name to change */
@optname varchar(35) = NULL,  /* option name to turn on/off */
@optvalue varchar(10) = NULL  /* true or false */
as

set nocount    on

declare @dbid int         /* dbid of the database */
declare @catvalue int     /* number of category option */
declare @optcount int      /* number of options like @optname */
declare @allstatopts int    /* bit map off all options stored in sysdatqabases.status
                            ** that can be set by sp_dboption. */
declare @alloptopts int    /* bit map off all options stored in sysdatqabases.status
                            ** that can be set by sp_dboption. */
declare @allcatopts int    /* bit map off all options stored in sysdatqabases.category
                            ** that can be set by sp_dboption. */
declare @exec_stmt nvarchar(550)
declare @fulloptname varchar(35)
declare @alt_optname varchar(50)
declare @alt_optvalue varchar(30)

declare @status int

/*
**  If no @dbname given, just list the possible dboptions.
**  Only certain status bits may be set or cleared by sp_dboption.
*/

/*
** Get bitmap of all options that can be set by sp_dboption.
*/
select @allstatopts=number from master.dbo.spt_values where type = 'D'
   and name = 'ALL SETTABLE OPTIONS'

select @allcatopts=number from master.dbo.spt_values where type = 'DC'
   and name = 'ALL SETTABLE OPTIONS'

select @alloptopts=number from master.dbo.spt_values where type = 'D2'
   and name = 'ALL SETTABLE OPTIONS'

if @dbname is null
begin
   select 'Settable database options:' = name
      from master.dbo.spt_values
      where (type = 'D'
            and number & @allstatopts <> 0
            and number not in (0,@allstatopts))  /* Eliminate non-option entries */
         or (type = 'DC'
            and number & @allcatopts <> 0
            and number not in (0,@allcatopts))
         or (type = 'D2'
            and number & @alloptopts <> 0
            and number not in (0,@alloptopts))
      order by name
   return (0)
end

/*
**  Verify the database name and get info
*/
select @dbid = dbid
from master.dbo.sysdatabases
where name = @dbname

/*
**  If @dbname not found, say so and list the databases.
*/
if @dbid is null
   begin
      raiserror(15010,-1,-1,@dbname)
      print ' '
      select 'Available databases:' = name
         from master.dbo.sysdatabases
      return (1)
   end

/*
** If no option was supplied, display current settings.
*/
if @optname is null
   begin
      select 'The following options are set:' = v.name
         from master.dbo.spt_values v, master.dbo.sysdatabases d
            where d.name=@dbname
               and ((number & @allstatopts <> 0
                     and number not in (-1,@allstatopts)
                     and v.type = 'D'
                     and (v.number & d.status)=v.number)
                 or (number & @allcatopts <> 0
                     and number not in (-1,@allcatopts)
                     and v.type = 'DC'
                     and d.category & v.number <> 0)
                 or (number & @alloptopts <> 0
                     and number not in (-1,@alloptopts)
                     and v.type = 'D2'
                     and d.status2 & v.number <> 0))
      return(0)
   end


if lower(@optvalue) not in ('true', 'false', 'on', 'off') and @optvalue is not null
   begin
      raiserror(15241,-1,-1)
      return (1)
   end

/*
**  Use @optname and try to find the right option.
**  If there isn't just one, print appropriate diagnostics and return.
*/
select @optcount = count(*) ,@fulloptname = min(name)
      from master.dbo.spt_values
      where lower(name) like '%' + lower(@optname) + '%'
         and ((type = 'D'
              and number & @allstatopts <> 0
              and number not in (-1,@allstatopts))
          or (type = 'DC'
              and number & @allcatopts <> 0
              and number not in (-1,@allcatopts))
            or (type = 'D2'
              and number & @alloptopts <> 0
              and number not in (-1,@alloptopts)))

/*
**  If no option, show the user what the options are.
*/
if @optcount = 0
   begin
      raiserror(15011,-1,-1,@optname)
      print ' '

      select 'Settable database options:' = name
         from master.dbo.spt_values
         where (type = 'D'
               and number & @allstatopts <> 0
               and number not in (-1,@allstatopts))  /* Eliminate non-option entries */
            or (type = 'DC'
               and number & @allcatopts <> 0
               and number not in (-1,@allcatopts))
            or (type = 'D2'
               and number & @alloptopts <> 0
               and number not in (-1,@alloptopts))
         order by name

      return (1)
   end


/*
**  If more than one option like @optname, show the duplicates and return.
*/
if @optcount > 1
   begin
      raiserror(15242,-1,-1,@optname)
      print ' '

      select duplicate_options = name
         from master.dbo.spt_values
         where lower(name) like '%' + lower(@optname) + '%'
            and ((type = 'D'
                 and number & @allstatopts <> 0
                 and number not in (-1,@allstatopts))
              or (type = 'DC'
                 and number & @allcatopts <> 0
                 and number not in (-1,@allcatopts))
              or (type = 'D2'
                 and number & @alloptopts <> 0
                 and number not in (-1,@alloptopts))
                )
      return (1)
   end


/*
**  Just want to see current setting of specified option.
*/
if @optvalue is null
begin
      select OptionName = v.name

            ,CurrentSetting =
               CASE
                  When ( ((v.number & d.status) = v.number
                          and v.type = 'D')
                      or (d.category & v.number <> 0
                           and v.type = 'DC')
                      or (d.status2 & v.number <> 0
                           and v.type = 'D2')
                       )
                     Then 'ON'
                  When NOT
                       ( ((v.number & d.status) = v.number
                          and v.type = 'D')
                      or (d.category & v.number <> 0
                           and v.type = 'DC')
                      or (d.status2 & v.number <> 0
                           and v.type = 'D2')
                       )
                     Then 'off'
               END

         from master.dbo.spt_values v, master.dbo.sysdatabases d
            where d.name=@dbname
               and ((v.number & @allstatopts <> 0
                     and v.number not in (-1,@allstatopts)   /* Eliminate non-option entries */
                     and v.type = 'D')
                 or (v.number & @allcatopts <> 0
                     and v.number not in (-1,@allcatopts)   /* Eliminate non-option entries */
                     and v.type = 'DC')
                 or (v.number & @alloptopts <> 0
                     and v.number not in (-1,@alloptopts)   /* Eliminate non-option entries */
                     and v.type = 'D2')
                   )
                and lower(v.name) = lower(@fulloptname)

   return (0)
end


select @catvalue = 0
select @catvalue = number
      from master.dbo.spt_values
      where lower(name) = lower(@fulloptname)
      and type = 'DC'

/* if setting replication option, call sp_replicationdboption directly */
if (@catvalue <> 0)
    begin
        if lower(@optvalue) in ('true', 'on')
            begin
                select @alt_optvalue = 'true'
            end
        else
            begin
                select @alt_optvalue = 'false'
            end

        select alt_optname = quotename(@fulloptname, '''')
        select @exec_stmt = quotename(@dbname, '[')   + '.dbo.sp_replicationdboption'

        if @catvalue = 1
            begin
                select @alt_optname  = 'publish'
            end
        if @catvalue = 2
            begin
                select @alt_optname  = 'subscribe'
            end
        if @catvalue = 4
            begin
                select @alt_optname  = 'merge publish'
            end

        exec @exec_stmt @dbname, @alt_optname, @alt_optvalue
        return (0)
    end


/* call Alter Database to set options */

/* set option value in alter database*/
if lower(@optvalue) in ('true', 'on')
   begin
        select @alt_optvalue = 'ON'
   end

else
    begin
        select @alt_optvalue = 'OFF'
    end

/* if Cross DB Ownership Chaining is the option. Set it and get out */
if lower(@fulloptname) = 'db chaining'
    begin

    -- CHECK PERMISSIONS (Note: All sysadmins are dbo) --
    if not (is_srvrolemember('sysadmin') = 1)
    begin
        raiserror(15247,-1,-1)
        return(1)
    end

    -- CANT SET IN ANY OF OF MASTER/MODEL/TEMPDB --
    if lower(@dbname) in ('master', 'model', 'tempdb') or
       (lower(@dbname) ='msdb' and @alt_optvalue = 'OFF')
    begin
        raiserror(5600,-1,-1)
        return(1)
    end

    -- ERROR IF IN USER TRANSACTION --
    if @@trancount > 0
    begin
        raiserror(15289,-1,-1)
        return (1)
    end

   -- MAKE THE FOLLOWING REMOVE/REMAP/DELETES ATOMIC --
    begin transaction

    select @status = status2 from master.dbo.sysdatabases where name = @dbname
    if @alt_optvalue = 'ON'
        set @status = @status | 1024
    else
        select @status = @status & (~ 1024)
    update master.dbo.sysdatabases set status2 = @status where name = @dbname


    -- REFLECT NEW STATUS IN SYSDATABASES --
    commit transaction

    -- CHECKPOINT DATABASE TO FORCE CHANGES TO IN-MEMORY STRUCTURE --
    select @exec_stmt = 'use ' +  quotename(@dbname, '[')   + ' checkpoint'
    exec (@exec_stmt)

    return (0) -- end of set db chaining/user info in doubt  option
    end


/* set option name in alter database */
if lower(@fulloptname) = 'auto create statistics'
    begin
        select @alt_optname = 'AUTO_CREATE_STATISTICS'
    end

if lower(@fulloptname) = 'auto update statistics'
    begin
        select @alt_optname = 'AUTO_UPDATE_STATISTICS'
    end

if lower(@fulloptname) = 'autoclose'
    begin
        select @alt_optname = 'AUTO_CLOSE'
    end

if lower(@fulloptname) = 'autoshrink'
    begin
        select @alt_optname = 'AUTO_SHRINK'
    end

if lower(@fulloptname) = 'ansi padding'
    begin
        select @alt_optname = 'ANSI_PADDING'
    end

if lower(@fulloptname) = 'arithabort'
    begin
        select @alt_optname = 'ARITHABORT'
    end

if lower(@fulloptname) = 'numeric roundabort'
    begin
        select @alt_optname = 'NUMERIC_ROUNDABORT'
    end

if lower(@fulloptname) = 'ansi null default'
    begin
        select @alt_optname = 'ANSI_NULL_DEFAULT'
    end

if lower(@fulloptname) = 'ansi nulls'
    begin
        select @alt_optname = 'ANSI_NULLS'
    end

if lower(@fulloptname) = 'ansi warnings'
    begin
        select @alt_optname = 'ANSI_WARNINGS'
    end

if lower(@fulloptname) = 'concat null yields null'
    begin
        select @alt_optname = 'CONCAT_NULL_YIELDS_NULL'
    end

if lower(@fulloptname) = 'cursor close on commit'
    begin
        select @alt_optname = 'CURSOR_CLOSE_ON_COMMIT'
    end

if lower(@fulloptname) = 'torn page detection'
    begin
        select @alt_optname = 'TORN_PAGE_DETECTION'
    end

if lower(@fulloptname) = 'quoted identifier'
    begin
        select @alt_optname = 'QUOTED_IDENTIFIER'
    end

if lower(@fulloptname) = 'recursive triggers'
    begin
        select @alt_optname = 'RECURSIVE_TRIGGERS'
    end

if lower(@fulloptname) = 'default to local cursor'
    begin
        select @alt_optname = 'CURSOR_DEFAULT'

        if @alt_optvalue = 'ON'
           begin
                select @alt_optvalue = 'LOCAL'
           end

        else
            begin
                select @alt_optvalue = 'GLOBAL'
            end
    end

if lower(@fulloptname) = 'offline'
    begin
        if @alt_optvalue = 'ON'
           begin
                select @alt_optname = 'OFFLINE'
           end

        else
            begin
                select @alt_optname = 'ONLINE'
            end
        select @alt_optvalue = ''
    end

if lower(@fulloptname) = 'read only'
    begin
        if @alt_optvalue = 'ON'
           begin
                select @alt_optname = 'READ_ONLY'
           end

        else
            begin
                select @alt_optname = 'READ_WRITE'
            end
        select @alt_optvalue = ''
    end

if lower(@fulloptname) = 'dbo use only'
    begin
        if @alt_optvalue = 'ON'
            begin
                if databaseproperty(@dbname, 'IsSingleUser') = 1
                    begin
                        raiserror(5066,-1,-1);
                        return (1)
                    end
                select @alt_optname = 'RESTRICTED_USER'
            end

        else
            begin
                if databaseproperty(@dbname, 'IsDBOOnly') = 0
                    begin
                        return (0)
                    end
                select @alt_optname = 'MULTI_USER'
            end

        select @alt_optvalue = ''
    end

if lower(@fulloptname) = 'single user'
    begin
        if @alt_optvalue = 'ON'
           begin
                if databaseproperty(@dbname, 'ISDBOOnly') = 1
                    begin
                        raiserror(5066,-1,-1);
                        return (1)
                    end
                select @alt_optname = 'SINGLE_USER'
           end

        else
            begin
                if databaseproperty(@dbname, 'IsSingleUser') = 0
                    begin
                        return (0)
                    end
                select @alt_optname = 'MULTI_USER'
            end
        select @alt_optvalue = ''
    end

if lower(@fulloptname) = 'select into/bulkcopy'
    begin
        select @alt_optname = 'RECOVERY'

        if @alt_optvalue = 'ON'
           begin
                if databaseproperty(@dbname, 'IsTrunclog') = 1
                    begin
                        select @alt_optvalue = 'RECMODEL_70BACKCOMP'
                    end
                else
                    begin
                        select @alt_optvalue = 'BULK_LOGGED'
                    end
           end

        else
            begin
                if databaseproperty(@dbname, 'IsTrunclog') = 1
                    begin
                        select @alt_optvalue = 'SIMPLE'
                    end
                else
                    begin
                        select @alt_optvalue = 'FULL'
                    end
            end
    end

if lower(@fulloptname) = 'trunc. log on chkpt.'
    begin
        select @alt_optname = 'RECOVERY'

        if @alt_optvalue = 'ON'
            begin
                if databaseproperty(@dbname, 'IsBulkCopy') = 1
                    begin
                        select @alt_optvalue = 'RECMODEL_70BACKCOMP'
                    end
                else
                    begin
                        select @alt_optvalue = 'SIMPLE'
                    end
            end

        else
            begin
                if databaseproperty(@dbname, 'IsBulkCopy') = 1
                    begin
                        select @alt_optvalue = 'BULK_LOGGED'
                    end
                else
                    begin
                        select @alt_optvalue = 'FULL'
                    end
            end
    end

/* construct the ALTER DATABASE command string */
select @exec_stmt = 'ALTER DATABASE ' + quotename(@dbname) +' SET ' + @alt_optname + ' ' + @alt_optvalue + ' WITH NO_WAIT'
exec (@exec_stmt)

if @@error <> 0
    begin
        raiserror(15627,-1,-1)
        return (1)
    end
else
    begin
        return (0)
    end

return (0) -- sp_dboption
go
set ansi_nulls off
go
/*ANSI_NULLS OFF  for after creation of sp_dboption*/

checkpoint
go

grant execute on sp_dboption to public
go
--------------------------------------------------------------------------
------------------------------ sp_MS_marksystemobject -------------------------------
if object_id('sp_MS_marksystemobject', 'P') IS NOT NULL
    drop procedure sp_MS_marksystemobject
raiserror('create procedure sp_MS_marksystemobject ...',0,1)
go
-- FOR INTERNAL USE ONLY ... DO NOT DOCUMENT --
-- This procedure sets a bit in sysobjects.  This bit has no meaning, various
--    groups (starfigther, davinci, replication) use it for different things
-- MSQL makes no warranty, express or implied, on what objects will or will
--    not have this bit set.  Use at your own risk.
--
create procedure sp_MS_marksystemobject
    @objname    nvarchar(517)  -- 517 is max for two part name
as
    -- pre-stuff --
    set nocount on

    -- CHECK THE OBJECT NAME --
    if object_id(@objname, 'local') is null
    begin
        raiserror('sp_MS_marksystemobject: Invalid object name ''%ls''',0,1,@objname)
        return 1
    end

    -- CHECK THE OBJECT OWNER (MUST BE A SYSTEM USER) --
    if user_name(ObjectProperty(object_id(@objname, 'local'), 'ownerid'))
                not in ('dbo','INFORMATION_SCHEMA')
    begin
        raiserror('sp_MS_marksystemobject: Object must be owned by a system user.',0,1)
        return 1
    end

    -- DO THE UPDATE --
    begin tran
    dbcc LockObjectSchema(@objname)
    update sysobjects set status = status | 0xC0000000
                        where id = object_id(@objname, 'local')
    commit tran
    return @@error  -- sp_MS_marksystemobject
go
exec sp_MS_marksystemobject 'sp_MS_marksystemobject'
go
revoke execute on sp_MS_marksystemobject to public
go

--------------------------------------------------------------------------------
--    Turn CDOC for cross database ownership chaining on
--    Name has to match exactly
--------------------------------------------------------------------------------
go
exec sp_dboption 'msdb', 'DB Chaining', 'on'
go

if object_id('sp_linkedservers_rowset','P') IS NOT NULL
    drop procedure sp_linkedservers_rowset
go

create proc sp_linkedservers_rowset
    (
    @srvname     sysname
    )
as
    IF is_srvrolemember('sysadmin') = 1
    begin
        select
            SVR_NAME         = srvname,
            SVR_PRODUCT        = srvproduct,
            SVR_PROVIDERNAME    = providername,
            SVR_DATASOURCE        = datasource,
            SVR_PROVIDERSTRING = providerstring,
            SVR_LOCATION        = location,
            SVR_CATALOG        = catalog
        from master.dbo.sysservers
        where srvname = @srvname and (srvstatus & 128) = 128
        order by 1
    end
    ELSE
    begin
        select
            SVR_NAME         = srvname,
            SVR_PRODUCT        = srvproduct,
            SVR_PROVIDERNAME    = providername,
            SVR_DATASOURCE        = datasource,
            SVR_PROVIDERSTRING = NULL,
            SVR_LOCATION        = location,
            SVR_CATALOG        = catalog
        from master.dbo.sysservers
        where srvname = @srvname and (srvstatus & 128) = 128
        order by 1
    end
go


create proc sp_linkedservers_rowset;2
as
    IF is_srvrolemember('sysadmin') = 1
    begin
        select
            SVR_NAME         = srvname,
            SVR_PRODUCT        = srvproduct,
            SVR_PROVIDERNAME    = providername,
            SVR_DATASOURCE        = datasource,
            SVR_PROVIDERSTRING = providerstring,
            SVR_LOCATION        = location,
            SVR_CATALOG        = catalog
        from master.dbo.sysservers
        where (srvstatus & 128) = 128
        order by 1
    end
    ELSE
    begin
        select
            SVR_NAME         = srvname,
            SVR_PRODUCT        = srvproduct,
            SVR_PROVIDERNAME    = providername,
            SVR_DATASOURCE        = datasource,
            SVR_PROVIDERSTRING = NULL,
            SVR_LOCATION        = location,
            SVR_CATALOG        = catalog
        from master.dbo.sysservers
        where (srvstatus & 128) = 128
        order by 1
    end
go

grant execute on sp_linkedservers_rowset to public
go

if object_id('sp_linkedservers', 'P') is not null
    drop proc sp_linkedservers
go
raiserror(15339,-1,-1,'sp_linkedservers')
go
create proc sp_linkedservers as
    IF is_srvrolemember('sysadmin') = 1
    begin
        select
            SRV_NAME = srvname,
            SRV_PROVIDERNAME = providername,
            SRV_PRODUCT = srvproduct,
            SRV_DATASOURCE = datasource,
            SRV_PROVIDERSTRING = providerstring,
            SRV_LOCATION = location,
            SRV_CAT = catalog
        from master.dbo.sysservers
        order by 1
    end
    ELSE
    begin
        select
            SRV_NAME = srvname,
            SRV_PROVIDERNAME = providername,
            SRV_PRODUCT = srvproduct,
            SRV_DATASOURCE = datasource,
            SRV_PROVIDERSTRING = NULL,
            SRV_LOCATION = location,
            SRV_CAT = catalog
        from master.dbo.sysservers
        order by 1
    end
go

grant execute on sp_linkedservers to public
go

DENY SELECT (providerstring) ON sysservers TO PUBLIC
go


--------------------------------------------------------------------------------
--     SP_SETLOGIN USED BY AGENT
--------------------------------------------------------------------------------
if object_id('sp_setuserbylogin','X') IS NOT NULL
    exec sp_dropextendedproc 'sp_setuserbylogin'
execute sp_addextendedproc 'sp_setuserbylogin','(server internal)'
go
grant execute on sp_setuserbylogin to public
go
exec sp_MS_marksystemobject 'sp_setuserbylogin'
go
--------------------------------------------------------------------------------
--    ODBC/OLEDB Catalog Procs (INSTCAT.SQL)
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--    End of SQLDMO System Procedures (SQLDMO.SQL)
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
-- END OF FILE: Turn off marking of system objects.
--    DO NOT ADD ANYTHING AFTER THIS POINT
--------------------------------------------------------------------------------
exec sp_MS_upd_sysobj_category 2
go

exec sp_configure 'allow updates',0
go

reconfigure with override
go